﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;

public class DBHelper
{
    private string connectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["connString"].ConnectionString;

    public DBHelper()
    {
    }

    public DBHelper(string connstr)
    {
        connectionString = connstr;
    }

    /// <summary>
    /// 执行一条SQL语句,并返回受影响的行数
    /// </summary>
    /// <param name="strSql">待执行的sql语句</param>
    /// <returns></returns>
    public int executeNoneQuery(string strSql)
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = strSql;

                int i = comm.ExecuteNonQuery();
                conn.Close();
                return i;
            }
            catch (Exception ex)
            {
                conn.Close();
                dbExceptionLog(connectionString, strSql, ex);
                return 0;
            }
        }
    }

    /// <summary>
    /// 执行一条SQL语句,并返回受影响的行数
    /// </summary>
    /// <param name="strSql">待执行的sql语句</param>
    /// <param name="errmsg">错误信息</param>
    /// <returns></returns>
    public int executeNoneQuery(string strSql, ref string errmsg)
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = strSql;

                int i = comm.ExecuteNonQuery();
                conn.Close();

                errmsg = "";
                return i;
            }
            catch (Exception ex)
            {
                conn.Close();
                errmsg = ex.Message;
                dbExceptionLog(connectionString, strSql, ex);
                return 0;
            }
        }
    }

    /// <summary>
    /// 执行一条SQL语句,并返回最后操作的值
    /// </summary>
    /// <param name="strSql">待执行的sql语句</param>
    /// <returns></returns>
    public int executeNoneQueryAndReturnLatestValue(string strSql)
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = strSql + ";select @@IDENTITY";

                object i = comm.ExecuteScalar();
                conn.Close();
                return int.Parse(i.ToString());
            }
            catch (Exception ex)
            {
                conn.Close();
                dbExceptionLog(connectionString, strSql + ";select @@IDENTITY", ex);
                return 0;
            }
        }
    }

    /// <summary>
    /// 执行一条SQL语句,并返回最后操作的值
    /// </summary>
    /// <param name="strSql">待执行的sql语句</param>
    /// <param name="errmsg">错误信息</param>
    /// <returns></returns>
    public int executeNoneQueryAndReturnLatestValue(string strSql, ref string errmsg)
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = strSql + ";select @@IDENTITY";

                object i = comm.ExecuteScalar();
                conn.Close();
                errmsg = "";
                return int.Parse(i.ToString());
            }
            catch (Exception ex)
            {
                conn.Close();
                errmsg = ex.Message;
                dbExceptionLog(connectionString, strSql + ";select @@IDENTITY", ex);
                return 0;
            }
        }
    }

    /// <summary>
    /// 获取数据列表
    /// </summary>
    /// <param name="strSQL">sql语句</param>
    /// <returns></returns>
    public DataTable getList(string strSQL)
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = strSQL;

                SqlDataAdapter da = new SqlDataAdapter(comm);
                DataSet ds = new DataSet();
                da.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                conn.Close();
                dbExceptionLog(connectionString, strSQL, ex);
                return new DataTable();
            }
        }
    }

    /// <summary>
    /// 获取数据列表
    /// </summary>
    /// <param name="strSQL">sql语句</param>
    /// <param name="errmsg">错误信息</param>
    /// <returns></returns>
    public DataTable getList(string strSQL, ref string errmsg)
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = strSQL;

                SqlDataAdapter da = new SqlDataAdapter(comm);
                DataSet ds = new DataSet();
                da.Fill(ds);
                conn.Close();
                errmsg = "";
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                conn.Close();
                errmsg = ex.Message;
                dbExceptionLog(connectionString, strSQL, ex);
                return new DataTable();
            }
        }
    }

    /// <summary>
    /// 获取数据列表
    /// </summary>
    /// <param name="tableName">数据库表名</param>
    /// <param name="colnums">列名</param>
    /// <param name="filter">筛选条件</param>
    /// <param name="orderBy">排序</param>
    /// <returns></returns>
    public DataTable getList(string tableName, string colnums, string filter, string orderBy)
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            string commStr = "";
            if (string.IsNullOrEmpty(filter.Trim()))
            {
                commStr = "SELECT " + colnums + " FROM " + tableName + " ORDER BY " + orderBy;
            }
            else
            {
                commStr = "SELECT " + colnums + " FROM " + tableName + " WHERE " + filter + " ORDER BY " + orderBy;
            }

            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = commStr;

                SqlDataAdapter da = new SqlDataAdapter(comm);
                DataSet ds = new DataSet();
                da.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                conn.Close();
                dbExceptionLog(connectionString, commStr, ex);
                return new DataTable();
            }
        }
    }

    /// <summary>
    /// 获取数据列表
    /// </summary>
    /// <param name="tableName">数据库表名</param>
    /// <param name="colnums">列名</param>
    /// <param name="filter">筛选条件</param>
    /// <param name="orderBy">排序</param>
    /// <param name="errmsg">错误信息</param>
    /// <returns></returns>
    public DataTable getList(string tableName, string colnums, string filter, string orderBy, ref string errmsg)
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            string commStr = "";
            if (string.IsNullOrEmpty(filter.Trim()))
            {
                commStr = "SELECT " + colnums + " FROM " + tableName + " ORDER BY " + orderBy;
            }
            else
            {
                commStr = "SELECT " + colnums + " FROM " + tableName + " WHERE " + filter + " ORDER BY " + orderBy;
            }

            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = commStr;

                SqlDataAdapter da = new SqlDataAdapter(comm);
                DataSet ds = new DataSet();
                da.Fill(ds);
                conn.Close();
                errmsg = "";
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                conn.Close();
                errmsg = ex.Message;
                dbExceptionLog(connectionString, commStr, ex);
                return new DataTable();
            }
        }
    }

    /// <summary>
    /// 获取数据列表
    /// </summary>
    /// <param name="top">数据行数</param>
    /// <param name="tableName">数据库表名</param>
    /// <param name="colnums">列名</param>
    /// <param name="filter">筛选条件</param>
    /// <param name="orderBy">排序</param>
    /// <returns></returns>
    public DataTable getList(string top, string tableName, string colnums, string filter, string orderBy)
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            string commStr = "";
            if (string.IsNullOrEmpty(filter.Trim()))
            {
                commStr = "SELECT TOP " + top + " " + colnums + " FROM " + tableName + " ORDER BY " + orderBy;
            }
            else
            {
                commStr = "SELECT TOP " + top + " " + colnums + " FROM " + tableName + " WHERE " + filter + " ORDER BY " + orderBy;
            }

            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = commStr;

                SqlDataAdapter da = new SqlDataAdapter(comm);
                DataSet ds = new DataSet();
                da.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                conn.Close();
                dbExceptionLog(connectionString, commStr, ex);
                return new DataTable();
            }
        }
    }

    /// <summary>
    /// 获取数据列表
    /// </summary>
    /// <param name="top">数据行数</param>
    /// <param name="tableName">数据库表名</param>
    /// <param name="colnums">列名</param>
    /// <param name="filter">筛选条件</param>
    /// <param name="orderBy">排序</param>
    /// <param name="errmsg">错误信息</param>
    /// <returns></returns>
    public DataTable getList(string top, string tableName, string colnums, string filter, string orderBy, ref string errmsg)
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            string commStr = "";
            if (string.IsNullOrEmpty(filter.Trim()))
            {
                commStr = "SELECT TOP " + top + " " + colnums + " FROM " + tableName + " ORDER BY " + orderBy;
            }
            else
            {
                commStr = "SELECT TOP " + top + " " + colnums + " FROM " + tableName + " WHERE " + filter + " ORDER BY " + orderBy;
            }

            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = commStr;

                SqlDataAdapter da = new SqlDataAdapter(comm);
                DataSet ds = new DataSet();
                da.Fill(ds);
                conn.Close();
                errmsg = "";
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                conn.Close();
                errmsg = ex.Message;
                dbExceptionLog(connectionString, commStr, ex);
                return new DataTable();
            }
        }
    }

    /// <summary>
    /// 获取记录总数
    /// </summary>
    /// <param name="tableName">数据库表名</param>
    /// <param name="filter">筛选条件</param>
    /// <returns></returns>
    public int getCount(string tableName, string filter)
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            string commStr = "";
            if (string.IsNullOrEmpty(filter.Trim()))
            {
                commStr = "SELECT COUNT(1) AS [count] FROM " + tableName + "";
            }
            else
            {
                commStr = "SELECT COUNT(1) AS [count] FROM " + tableName + " WHERE " + filter;
            }

            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = commStr;

                object c = comm.ExecuteScalar();
                conn.Close();
                return int.Parse(c.ToString());
            }
            catch (Exception ex)
            {
                conn.Close();
                dbExceptionLog(connectionString, commStr, ex);
                return 0;
            }
        }
    }

    /// <summary>
    /// 获取记录总数
    /// </summary>
    /// <param name="tableName">数据库表名</param>
    /// <param name="filter">筛选条件</param>
    /// <param name="errmsg">错误信息</param>
    /// <returns></returns>
    public int getCount(string tableName, string filter, ref string errmsg)
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            string commStr = "";
            if (string.IsNullOrEmpty(filter.Trim()))
            {
                commStr = "SELECT COUNT(1) AS [count] FROM " + tableName + "";
            }
            else
            {
                commStr = "SELECT COUNT(1) AS [count] FROM " + tableName + " WHERE " + filter;
            }

            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = commStr;

                object c = comm.ExecuteScalar();
                conn.Close();
                errmsg = "";
                return int.Parse(c.ToString());
            }
            catch (Exception ex)
            {
                conn.Close();
                errmsg = ex.Message;
                dbExceptionLog(connectionString, commStr, ex);
                return 0;
            }
        }
    }

    /// <summary>
    /// 数据源为Excel
    /// </summary>
    /// <param name="excelPath">Excel路径</param>
    /// <param name="sheetName">Excel sheet名称</param>
    /// <returns></returns>
    public DataTable getDataSetFromExcel(string excelPath, string sheetName)
    {
        DataSet ds = new DataSet();

        string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties = \"Excel 12.0 Xml;HDR=YES;IMEX=0\";";
        string commStr = "SELECT * FROM [" + sheetName + "$]";

        OleDbConnection conn = new OleDbConnection(excelConnectionString);

        try
        {
            conn.Open();
            OleDbCommand comm = new OleDbCommand();
            comm.Connection = conn;
            comm.CommandText = commStr;

            OleDbDataAdapter oda = new OleDbDataAdapter(comm);
            oda.Fill(ds);

            conn.Close();
        }
        catch (Exception ex)
        {
            conn.Close();
            dbExceptionLog(excelConnectionString, commStr, ex);
        }

        return ds.Tables[0];
    }

    /// <summary>
    /// 数据源为Excel
    /// </summary>
    /// <param name="excelPath">Excel路径</param>
    /// <param name="sheetName">Excel sheet名称</param>
    /// <param name="errmsg">错误信息</param>
    /// <returns></returns>
    public DataTable getDataSetFromExcel(string excelPath, string sheetName, ref string errmsg)
    {
        DataSet ds = new DataSet();

        string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties = \"Excel 12.0 Xml;HDR=YES;IMEX=0\";";
        string commStr = "SELECT * FROM [" + sheetName + "$]";

        OleDbConnection conn = new OleDbConnection(excelConnectionString);

        try
        {
            conn.Open();
            OleDbCommand comm = new OleDbCommand();
            comm.Connection = conn;
            comm.CommandText = commStr;

            OleDbDataAdapter oda = new OleDbDataAdapter(comm);
            oda.Fill(ds);
            errmsg = "";
            conn.Close();
        }
        catch (Exception ex)
        {
            conn.Close();
            errmsg = ex.Message;
            dbExceptionLog(excelConnectionString, commStr, ex);
        }

        return ds.Tables[0];
    }

    private void dbExceptionLog(string connString, string commandText, Exception ex)
    {
        LogHelper.Log("SQL语句:" + commandText);
        LogHelper.Log("数据库错误:" + commandText);
    }
}
